/**
 * Created by xc on 15/12/22.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var CONFIG = require('../../config');


// Schema 结构
var ItbCollectModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`collect_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`doc_id` bigint(20) NOT NULL COMMENT '文档id',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
ItbCollectModel.prototype = new ItbBaseModel();

// 取得表名
ItbCollectModel.prototype.getTableName = function(){
    return 'itb_collect';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbCollectModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'collect_id='+this.collect_id;
}
// 取消收藏
ItbCollectModel.prototype.deleteCollectLogic = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE '+tableName+' SET del_flg=1, update_time=sysdate(), update_by=? ' +
            ' WHERE user_id=? and doc_id=?';
        var sqlValue = [THIS.update_by, THIS.user_id, THIS.doc_id];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                callback(error);
            } else {
                callback(null);
            }
        });
    });
}

// 获取收藏文档信息
ItbCollectModel.prototype.getCollectInfoList = function(callback,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT x.doc_id, x.doc_name, x.page_count, x.create_time, x.thumb_s, y.grp_type_name '+
            ' FROM '+

            ' (SELECT d.doc_id, d.doc_name, p.thumb_s, p2.page_count, '+
            '     DATE_FORMAT(d.create_time, '+"'%Y/%m/%d %H:%i:%S'"+') AS create_time '+
            ' FROM itb_document d, '+
            '      itb_collect c, '+
            '      itb_page p, '+
            '     (SELECT t.doc_id, count(1) AS page_count '+
            ' FROM itb_page t '+
            ' WHERE t.del_flg = 0 '+
            ' GROUP BY t.doc_id) p2 '+
            ' WHERE d.doc_id = p.doc_id '+
            ' AND p.page_index = 0 '+
            ' AND p2.doc_id = d.doc_id '+
            ' AND c.doc_id = d.doc_id '+
            ' AND c.user_id = ? '+
            ' AND c.del_flg = 0 '+
            ' AND d.del_flg = 0 '+
            ' AND p.del_flg = 0) x '+

            ' LEFT JOIN '+

            ' (SELECT t1.doc_id, '+
            '     GROUP_CONCAT(t2.type_name) AS grp_type_name '+
            ' FROM itb_doc_type_r t1, '+
            '     itb_doc_type t2 '+
            ' WHERE t1.type_id = t2.type_id '+
            ' AND t1.del_flg = 0 '+
            ' AND t2.del_flg = 0 '+
            ' GROUP BY t1.doc_id) y '+

            ' ON x.doc_id = y.doc_id ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        var sqlValue = [THIS.user_id];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//===================================================5.4新增收藏功能相关==================================================
// todo 新增方法, 待审核 begin
/**
 * 获取收藏列表 sql
 * @param bForCnt 是否用户获取数据总个数
 * @param userId  查询的用户 id
 * @param pageNum 查询指定页
 * @param pageSize 查询页大小
 * @return 对应类型的 sql
 * */
function sql_collectList(bForCnt, userId, pageNum, pageSize){

    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(*) AS count';
    } else {
        sql += ' SELECT * ';
    }

    sql += " FROM "  +
        "   (SELECT c.type AS typeL, "  +
        "           c.item_id AS id, "  +
        "           c.collect_id AS collect_id, "  +
        "           i.item_name AS name, "  +
        "           (SELECT t.type_name FROM tg_type t WHERE t.type_id=tt.parent AND del_flg=0) AS typeDesc, "  +
        "           func_parse_string_array(i.thumbs,0) AS thumb, "  +
        "           DATE_FORMAT(c.update_time, '%Y/%m/%d') AS time, " +
        "           c.update_time AS order_time, "  +
        "           u.nickname AS author "  +
        "    FROM itb_collect c, "  +
        "         itb_items i, "  +
        "         tg_design_doc d, "  +
        "         itb_user u, "  +
        "         tg_type tt "  +
        "    WHERE c.user_id = "  + userId +
        "      AND c.del_flg=0 "  +
        "      AND c.type=4 "  +
        "      AND i.item_id = c.item_id "  +
        "      AND i.state = 20 " + // 上架的商品
        "      AND i.del_flg=0 "  +
        "      AND d.design_id = c.tmpl_id "  +
        "      AND d.publish = 1 " + // 发布的模板
        "      AND d.del_flg=0 "  +
        "      AND u.user_id=d.user_id "  +
        "      AND u.del_flg=0 "  +
        "      AND tt.type_id=d.type_id "  +
        "      AND tt.del_flg=0 "  +
        "    UNION ALL SELECT c.type AS typeL, "  +
        "                     c.tmpl_id AS id, "  +
        "                     c.collect_id AS collect_id, "  +
        "                     d.design_name AS name, "  +
        "                    CASE " +
        "                        WHEN tt.top_parent=1 THEN '单页' " +
        //"                        WHEN tt.top_parent=6 THEN '社交媒体' " + // 只有单页, ppt 是一级分类, 其余为二级分类 remove by wangjun at 2017-07-25
        "                        ELSE tt.type_name "+
        "                    END AS typeDesc, " +
        "                     dp.thumb AS thumb, "  +
        "           DATE_FORMAT(c.update_time, '%Y/%m/%d') AS time, " +
        "                     c.update_time AS order_time, "  +
        "                     u.nickname AS author "  +
        "    FROM itb_collect c, "  +
        "         tg_design_doc d, "  +
        "         tg_design_page dp, "  +
        "         tg_type tt, "  +
        "         itb_user u "  +
        "    WHERE c.user_id = "  + userId +
        "      AND c.del_flg=0 "  +
        "      AND c.type IN (1,6,7,8) "  + // 支持的设计分类
        "      AND d.design_id = c.tmpl_id "  +
        "      AND d.publish = 1 " + // 发布的模板
        "      AND d.del_flg=0 "  +
        "      AND dp.design_id = c.tmpl_id "  +
        "      AND dp.page_index=0 "  +
        "      AND dp.del_flg=0 "  +
        "      AND tt.type_id=d.type_id "  +
        "      AND tt.del_flg=0 "  +
        "      AND u.user_id = d.user_id "  +
        "      AND u.del_flg=0 "  +
        // "    UNION ALL SELECT c.type AS typeL, "  +
        // "                     c.tmpl_id AS id, "  +
        // "                     c.collect_id AS collect_id, "  +
        // "                     t.tmpl_name AS name, "  +
        // "                      '幻灯片'  AS typeDesc, "  +
        // "                     tp.thumb_s AS thumb, "  +
        // "           DATE_FORMAT(c.update_time, '%Y/%m/%d') AS time, " +
        // "                     c.update_time AS order_time, "  +
        // "                     u.nickname AS author "  +
        // "    FROM itb_collect c, "  +
        // "         itb_template t, "  +
        // "         itb_tmpl_page tp, "  +
        // "         itb_user u "  +
        // "    WHERE c.user_id = "  + userId +
        // "      AND c.del_flg=0 "  +
        // "      AND c.type = 3 "  +
        // "      AND t.tmpl_id = c.tmpl_id "  +
        // "      AND t.del_flg=0 "  +
        // "      AND t.publish=1 "  + // 发布的模板
        // "      AND tp.tmpl_id=c.tmpl_id "  +
        // "      AND tp.page_index=0 "  +
        // "      AND tp.del_flg=0 "  +
        // "      AND u.user_id=t.user_id "  +
        // "      AND u.del_flg=0" +
        ") l ";

        if(!bForCnt) {
            var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
            sql += "  ORDER BY l.order_time DESC LIMIT "+skipCnt+", "+Number(pageSize||'0');
        }
        return sql;
}

/**
 * 获取查询收藏项目状态的 sql
 * @param type 插入项类型, 一级分类
 * @param userId 用户唯一标识
 * @param id 插入项唯一标识
 * @return 对应类型的 sql
 * */
function sql_checkCollectItemState(type, userId, id) {
    /**
     1 海报  2 多页  3 ppt.  4. 印品  6.社交媒体, 7 电商
     CONFIG.DOC_TYPE_1 = 1;
     CONFIG.DOC_TYPE_2 = 2;
     CONFIG.DOC_TYPE_3 = 3;
     CONFIG.DOC_TYPE_4 = 4;
     CONFIG.DOC_TYPE_6 = 6;
     CONFIG.DOC_TYPE_7 = 7;
     * */
    var sql = '';
    if(type == CONFIG.DOC_TYPE_1 || type == CONFIG.DOC_TYPE_6 || type == CONFIG.DOC_TYPE_7 || type == CONFIG.DOC_TYPE_8) {
        // 设计
        sql += " SELECT count(d.design_id) AS canCollect, "  +
        "      count(c.collect_id) AS isCollected, "  +
        "      d.design_id AS tmpl_id " +
        " FROM tg_design_doc d "  +
        " LEFT JOIN itb_collect c ON c.type= " + type  +
        " AND c.tmpl_id=" +id  +
        " AND c.user_id=" +userId +
        " AND c.del_flg=0 "  +
        " WHERE d.design_id=" + id +
        " AND d.publish=1 "  +
        " AND d.del_flg=0 ";
    } else if(type == CONFIG.DOC_TYPE_3) {
        //ppt
        sql += " SELECT count(t.tmpl_id) AS canCollect, "  +
        "        count(c.collect_id) AS isCollected, "  +
        "        t.tmpl_id AS tmpl_id " +
        " FROM itb_template t "  +
        " LEFT JOIN itb_collect c ON c.type= " + type +
        " AND c.tmpl_id=" + id +
        " AND c.user_id=" +userId +
        " AND c.del_flg=0 "  +
        " WHERE t.tmpl_id =" + id +
        "   AND t.publish=1  "  +
        "   AND t.del_flg=0 ";
    } else if(type == CONFIG.DOC_TYPE_4) {
        //商品
        sql += " SELECT count(d.design_id) AS canCollect, "  +
        "       count(c.collect_id) AS isCollected, "  +
        "      	d.design_id AS tmpl_id, " +
        "	    d.item_id AS item_id" +
        " FROM tg_design_doc d, "  +
        "    itb_items i "  +
        " LEFT JOIN itb_collect c ON c.type=" + type +
        " AND c.item_id=" + id  +
        " AND c.user_id=" +userId +
        " AND c.tmpl_id=tmpl_id "  +
        " AND c.del_flg=0 "  +
        " WHERE d.item_id=" + id  +
        " AND d.publish=1 "  +
        " AND d.del_flg=0 "  +
        " AND i.state=20 "  +
        " AND i.item_id=" + id  +
        " AND i.del_flg=0 ";
    }
    return sql;
}

/**
 * 获取删除收藏项目的 sql
 * @param type: 收藏类型
 * @param userId: 用户唯一标识
 * @param id: 对应类型的收藏标识
 * */
function sql_removeUserCollect(type, userId, id) {

    var sql = " UPDATE itb_collect c " +
        " SET c.del_flg=1, " +
        "    c.update_time = SYSDATE(), " +
        "    c.update_by=" + userId+
        " WHERE c.type=" + type+
        "  AND c.user_id="+ userId +
        "  AND c.del_flg=0 ";
    if(type == CONFIG.DOC_TYPE_1 || type == CONFIG.DOC_TYPE_6 || type == CONFIG.DOC_TYPE_3 || type == CONFIG.DOC_TYPE_7 || type == CONFIG.DOC_TYPE_8) {
        // 设计
        sql += "  AND c.tmpl_id=" + id;
    } else if(type == CONFIG.DOC_TYPE_4) {
        sql += "  AND c.item_id=" + id;
    }
    return sql;
}

/**
 * 获取用户收藏列表
 * 参数: userId 用户唯一标识
 * 参数: pageNum 分页页码
 * 参数: pageSize 分页尺寸
 * 返回数组:    [{
                "thumb":"http://xxx.jpg",
                "id":788,
                "typeL":1,
                "name" : "快递开业通知模板",
                "typeDesc":"名片",
                “author":"by乌头马角工作室",
                "time" : "2016-12-05 13:38"  // 收藏时间
            }]
 * */
ItbCollectModel.prototype.getUserCollectList = function(userId, pageNum, pageSize, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCollectModel.js [getUserCollectList] Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = sql_collectList(false, userId, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

/**
 * 获取个人收藏列表项目总个数
 * */
ItbCollectModel.prototype.getUserCollectListCnt = function (userId, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCollectModel.js [getUserCollectListCnt] Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = sql_collectList(true, userId, null, null);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length > 0) {
                 callback(error, result[0].count);
            }else {
                callback(error, result);
            }
        });
    });
};

/**
 * 查询  收藏项状态
 * 参数 type: 收藏项目的一级分类
 * 参数 userId: 收藏发起者的唯一标识
 * 参数 id: 收藏项目的唯一标识
 *
 * callback
 *      error: 错误标识
 *      chkResult
 *          canCollect: 收藏项目是否有效, 0 无效, 不可收藏, 1 有效, 可以收藏
 *          isCollected: 用户是否已收藏该项目, 0, 未收藏, 1 收藏
 * */
ItbCollectModel.prototype.checkCollectItemState = function(type, userId, id, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCollectModel.js [checkItemCanCollect] Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = sql_checkCollectItemState(type, userId, id);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(result && result.length > 0) {
                callback(error, result[0]);
            }else {
                callback(error, result);
            }
        });
    });
};

/**
 * 插入新的收藏项目
 * 参数 type: 收藏项目一级分类
 * 参数 userId : 用户唯一标识
 * 参数 tmpl_id: 模板唯一标识
 * 参数 item_id: 商品唯一标识
 * */
ItbCollectModel.prototype.userCollectItem = function(type, userId, tmpl_id, item_id, callback) {

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCollectModel.js [userCollectItem] Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = "INSERT INTO itb_collect (type, user_id, tmpl_id, item_id, update_by, create_time, update_time) value ( "+type+" , "+userId+" , " + tmpl_id+" , "+ item_id+" , "+userId+" , SYSDATE(), SYSDATE()) ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

/**
 * 移除用户的收藏项目
 * 参数 type: 收藏项目一级分类
 * 参数 userId : 用户唯一标识
 * 参数 tmpl_id: 模板唯一标识
 * 参数 item_id: 商品唯一标识
 * */
ItbCollectModel.prototype.removeUserCollect = function(type, userId, id, callback) {
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCollectModel.js [removeUserCollect] Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var sql = sql_removeUserCollect(type, userId, id);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};
// todo 新增方法, 待审核 end





// 导出
module.exports = ItbCollectModel;